import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

try:
    # 检查并安装必要模块
    try:
        import pandas
        import openpyxl
    except ImportError:
        import subprocess
        import sys
        subprocess.check_call([sys.executable, "-m", "pip", "install", "-i", "https://pypi.tuna.tsinghua.edu.cn/simple", "pandas", "openpyxl", "matplotlib"])
    
    # 读取Excel文件
    data = pd.read_excel('FhjlViewDD.xlsx')
    
    # 转换日期列并筛选6月数据
    data['创建时间'] = pd.to_datetime(data['创建时间'])
    june_data = data[data['创建时间'].dt.month == 6]
    
    # 按客户分组汇总货运量
    customer_freight = june_data.groupby('客户')['净重'].sum().sort_values(ascending=False)
    
    # 生成柱状图
    plt.figure(figsize=(12, 6))
    customer_freight.plot(kind='bar')
    plt.title('6月份客户货运需求量TOP20')
    plt.xlabel('客户')
    plt.ylabel('货运量(吨)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig('customer_freight_summary.png', dpi=300)
    plt.close()
    
    # 保存到Excel
    customer_freight.to_excel('customer_freight_summary.xlsx')
    
    print("分析完成，结果已保存到customer_freight_summary.xlsx和customer_freight_summary.png")
    
except FileNotFoundError:
    print("错误：未找到FhjlViewDD.xlsx文件")
except Exception as e:
    print(f"发生错误：{str(e)}")